package com.pizza.database.mapping;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.pizza.abstrait.AbstractAdresse;
import com.pizza.abstrait.AbstractClient;
import com.pizza.database.DBConnection;
import com.pizza.model.Adresse;
import com.pizza.model.Client;

/**
 * Class ClientDB, mapping avec la bdd.
 *
 */
public class ClientDB implements Serializable
{
	private DBConnection dbConnexion;

	public ClientDB(DBConnection _dbConnexion)
	{
		dbConnexion = _dbConnexion;
	}

	public int insertClient(Client _client)
	{
		int retour = 0;

		// Insertion de l'adresse de facturation
		AdresseDB adressesClient = new AdresseDB(dbConnexion);
		retour += adressesClient.insertAdresse(_client.getAdresseFacturation());

		if (_client.getAdressesLivraison().isEmpty())
			_client.getAdressesLivraison().add(_client.getAdresseFacturation());

		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("INSERT INTO clients");
		query
				.append(" (nom,prenom,mail,motdepasse,dateinscription,id_adressesfacturation)");
		query.append(" VALUES(?,?,?,?,?,?)");

		try
		{
			PreparedStatement pStatement = connexion.prepareStatement(query.toString());

			pStatement.setString(1, _client.getNom());
			pStatement.setString(2, _client.getPrenom());
			pStatement.setString(3, _client.getMail());
			pStatement.setString(4, _client.getMotdepasse());
			pStatement.setTimestamp(5, new java.sql.Timestamp(_client
					.getDateInscription().getTime()));
			pStatement.setInt(6, _client.getAdresseFacturation().getId());

			// Insertion du client
			retour += pStatement.executeUpdate();

			ResultSet rs = pStatement.getGeneratedKeys();
			rs.first();
			_client.setId(rs.getInt(1));
			rs.close();

			pStatement.close();

		} catch (SQLException e)
		{
			e.printStackTrace();
		}

		// Insertion des adresses de livraison
		retour += adressesClient.insertAdressesLivraison(_client);

		return retour;
	}

	public Client getClientById(int _id)
	{
		Connection connexion = dbConnexion.getConnection();
		Client retour = null;
		StringBuilder query = new StringBuilder();
		query
				.append("SELECT nom,prenom,mail,motdepasse,dateinscription,id_adressesfacturation");
		query.append(" FROM clients");
		query.append(" WHERE id=?");
		try
		{
			PreparedStatement pStatement = connexion.prepareStatement(query.toString());
			pStatement.setInt(1, _id);
			ResultSet rs = pStatement.executeQuery();
			if (rs.first())
			{
				String nom = rs.getString("nom");
				String prenom = rs.getString("prenom");
				String mail = rs.getString("mail");
				String motdepasse = rs.getString("motdepasse");
				Date dateinscription = rs.getDate("dateinscription");
				int id_adressesfacturation = rs.getInt("id_adressesfacturation");
				retour = new Client(_id, nom, prenom, mail, motdepasse, dateinscription,
						new AdresseDB(dbConnexion).getAdresseById(id_adressesfacturation));
				retour.getAdressesLivraison().addAll(
						getAdressesLivraisonFromClient(retour));
			}
			rs.close();
			pStatement.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return retour;
	}

	public Client getClientByMail(String _mail)
	{
		Connection connexion = dbConnexion.getConnection();
		Client retour = null;
		StringBuilder query = new StringBuilder();
		query
				.append("SELECT id,nom,prenom,motdepasse,dateinscription,id_adressesfacturation");
		query.append(" FROM clients");
		query.append(" WHERE mail=?");
		try
		{
			PreparedStatement pStatement = connexion.prepareStatement(query.toString());
			pStatement.setString(1, _mail);
			ResultSet rs = pStatement.executeQuery();
			if (rs.first())
			{
				int id = rs.getInt("id");
				String nom = rs.getString("nom");
				String prenom = rs.getString("prenom");
				String motdepasse = rs.getString("motdepasse");
				Date dateinscription = rs.getDate("dateinscription");
				int id_adressesfacturation = rs.getInt("id_adressesfacturation");
				retour = new Client(id, nom, prenom, _mail, motdepasse, dateinscription,
						new AdresseDB(dbConnexion).getAdresseById(id_adressesfacturation));
				retour.getAdressesLivraison().addAll(
						getAdressesLivraisonFromClient(retour));
			}
			rs.close();
			pStatement.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return retour;
	}

	public List<AbstractAdresse> getAdressesLivraisonFromClient(Client _client)
	{
		Connection connexion = dbConnexion.getConnection();
		List<AbstractAdresse> retour = new ArrayList<AbstractAdresse>();
		StringBuilder query = new StringBuilder();
		query.append("SELECT id_adresses");
		query.append(" FROM clients_adresseslivraison");
		query.append(" WHERE id_clients=?");

		try
		{
			PreparedStatement pStatement = connexion.prepareStatement(query.toString());
			pStatement.setInt(1, _client.getId());
			ResultSet rs = pStatement.executeQuery();
			while (rs.next())
			{
				int id_adresseLivraison = rs.getInt("id_adresses");
				retour
						.add(new AdresseDB(dbConnexion)
								.getAdresseById(id_adresseLivraison));
			}
			rs.close();
			pStatement.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return retour;
	}

	public int updateClient(AbstractClient _client)
	{
		int retour = 0;
		Connection connexion = dbConnexion.getConnection();
		StringBuilder queryupdate = new StringBuilder();
		StringBuilder queryselect = new StringBuilder();

		queryselect.append("SELECT * FROM clients");
		queryselect.append(" WHERE id=?");
		try
		{
			PreparedStatement pStatement = connexion.prepareStatement(queryselect
					.toString());
			pStatement.setInt(1, _client.getId());
			ResultSet rs = pStatement.executeQuery();
			if (rs.first())
			{
				queryupdate.append("UPDATE clients SET");
				boolean doUpdate = false;

				if (!rs.getString("nom").equals(_client.getNom()))
				{
					doUpdate = true;
					queryupdate.append(" nom='" + _client.getNom() + "',");
				}
				if (!rs.getString("prenom").equals(_client.getPrenom()))
				{
					doUpdate = true;
					queryupdate.append(" prenom='" + _client.getPrenom() + "',");
				}
				/*
				 * if (!rs.getString("mail").equals(_client.getMail()))
				 * queryupdate.append(" mail='" + _client.getMail() + "',");
				 */
				if (!rs.getString("motdepasse").equals(_client.getMotdepasse()))
				{
					doUpdate = true;
					queryupdate.append(" motdepasse='" + _client.getMotdepasse() + "',");
				}
				if (rs.getInt("id_adressesfacturation") != _client
						.getAdresseFacturation().getId())
				{
					doUpdate = true;
					queryupdate.append(" id_adressesfacturation="
							+ _client.getAdresseFacturation().getId());
				}
				if (queryupdate.charAt(queryupdate.length() - 1) == ',')
					queryupdate.deleteCharAt(queryupdate.length() - 1);
				queryupdate.append(" WHERE id=" + _client.getId());

				if (doUpdate)
					retour += connexion.createStatement().executeUpdate(
							queryupdate.toString());

				AdresseDB adressedb = new AdresseDB(dbConnexion);

				// Mise à jour de l'adresse de facturation si nécessaire
				Adresse adresseFacturation = adressedb.getAdresseById(_client
						.getAdresseFacturation().getId());
				if (!_client.getAdresseFacturation().equals(adresseFacturation))
					retour += adressedb.updateAdresse(_client.getAdresseFacturation());

				// Mise à jour des adresses de livraison si nécessaire
				Adresse adresseLivraison;
				for (int i = 0; i < _client.getAdressesLivraison().size(); i++)
				{
					adresseLivraison = adressedb.getAdresseById(_client
							.getAdressesLivraison().get(i).getId());
					if (!_client.getAdressesLivraison().get(i).equals(adresseFacturation))
						if (!_client.getAdressesLivraison().get(i).equals(
								adresseLivraison))
							retour += adressedb.updateAdresse(_client
									.getAdressesLivraison().get(i));
				}
			}
			rs.close();
			pStatement.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return retour;
	}
}
